package net.lab1024.sa.admin.util;

import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import net.lab1024.sa.admin.module.business.erp.domain.entity.TmsordEntity;
import net.lab1024.sa.admin.module.business.erp.domain.form.query.TmsordQueryForm;
import net.lab1024.sa.admin.module.business.erp.domain.vo.TmsordVO;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class AdminSqlserverDao {

    private static final Logger log = LoggerFactory.getLogger(AdminSqlserverDao.class);

    public static Connection dbConn;

    static {
        String dbURL = "jdbc:sqlserver://10.2.3.11:1433;DatabaseName=smart_admin_cost;encrypt=true;trustServerCertificate=true";//数据库路径
        String name = "sa";                                                            //数据库账号
        String password = "sp1234..";//数据库密码
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            //2.连接
            dbConn = DriverManager.getConnection(dbURL, name, password);
            System.out.println("连接数据库成功！");

        } catch (Exception e) {
            e.printStackTrace();
            System.out.println("连接数据库失败！");
        }
    }


    public static List<TmsordVO> queryPage(Page<?> page, TmsordQueryForm queryForm) {

        StringBuilder count = new StringBuilder("select count(*) total from tmsord where 1=1 ");
        StringBuilder stringBuilder = new StringBuilder("SELECT * FROM  (select row_number() over(order by doc_entry desc )  AS line_num , doc_entry,user_id,user_name,mac_id,mac_name,auth_date_time,auth_date,auth_time,direction,card_id from tmsord where 1=1  ");

        if (queryForm.getAuthDateTimeFr() != null) {
            stringBuilder.append(String.format(" and CONVERT(varchar(19), auth_date_time,20 )  >= CONVERT(varchar(100), '%s' ,20) ", queryForm.getAuthDateTimeFr()));
            count.append(String.format(" and CONVERT(varchar(19),  auth_date_time,20 )  >= CONVERT(varchar(100), '%s' ,20) ", queryForm.getAuthDateTimeFr()));
        }
        if (queryForm.getAuthDateTimeTo() != null) {
            stringBuilder.append(String.format(" and CONVERT(varchar(19),  auth_date_time,20 )  <= CONVERT(varchar(100), '%s' ,20) ", queryForm.getAuthDateTimeTo()));
            count.append(String.format(" and CONVERT(varchar(19),auth_date_time ,20 )  <= CONVERT(varchar(100), '%s' ,20) ", queryForm.getAuthDateTimeTo()));

        }
        if (queryForm.getUserName() != null && queryForm.getUserName().length() > 0) {
            stringBuilder.append(String.format(" and user_name = '%s'", queryForm.getUserName()));
            count.append(String.format(" and user_name = '%s'", queryForm.getUserName()));
        }

        if (queryForm.getAbsId() != null && queryForm.getAbsId().length() > 0) {
            stringBuilder.append(String.format(" and CONVERT(varchar(7), auth_date_time ,20 ) = '%s'", queryForm.getAbsId()));
            count.append(String.format(" and CONVERT(varchar(7), auth_date_time ,20 ) = '%s'", queryForm.getAbsId()));
        }

        stringBuilder.append(String.format("  ) t WHERE line_num > (%d - 1 ) * %d ", queryForm.getPageNum(), queryForm.getPageSize()));
        stringBuilder.append(String.format(" and  line_num <= %d * %d", queryForm.getPageNum(), queryForm.getPageSize()));

        stringBuilder.append(" order by doc_entry desc");

        log.info(stringBuilder.toString());
        ArrayList<TmsordVO> tmsordVOS = new ArrayList<>();
        try {
            ResultSet resultSet = dbConn.prepareStatement(count.toString()).executeQuery();
            while (resultSet.next()) {
                page.setTotal(resultSet.getInt("total"));
            }
            ResultSet res = dbConn.prepareStatement(stringBuilder.toString()).executeQuery();
            while (res.next()) {
                String docEntry = res.getString("doc_entry");
                String userId = res.getString("user_id");
                String userName = res.getString("user_name");
                String macName = res.getString("mac_name");
                String macId = res.getString("mac_id");
                String cardId = res.getString("card_id");
                String createDateTime = res.getString("auth_date_time");
                String createDate = res.getString("auth_date");
                String createTime = res.getString("auth_time");
                String direction = res.getString("direction");
                TmsordVO tmsordVO = new TmsordVO();
                tmsordVO.setDocEntry(Long.valueOf(docEntry));
                tmsordVO.setUserId(userId);
                tmsordVO.setUserName(userName);
                tmsordVO.setMacId(macId);
                tmsordVO.setMacName(macName);
                tmsordVO.setCardId(cardId);
                tmsordVO.setAuthDateTime(createDateTime);
                tmsordVO.setAuthDate(createDate);
                tmsordVO.setDirection(direction);
                tmsordVOS.add(tmsordVO);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return tmsordVOS;
    }


    public static void insert(TmsordEntity tmsordEntity) {
        String string = String.format("insert into tmsord(user_id,user_name,mac_name,mac_id,card_id,auth_date_time,auth_date,auth_time,direction) values('%s','%s','%s','%s','%s','%s','%s','%s','%s')", tmsordEntity.getUserId(), tmsordEntity.getUserName(), tmsordEntity.getMacName(), tmsordEntity.getMacId(), tmsordEntity.getCardId(), tmsordEntity.getAuthDateTime(), tmsordEntity.getAuthDate(), tmsordEntity.getAuthTime(), tmsordEntity.getDirection()).toString();
        log.info(string);
        try {
            dbConn.prepareStatement(string).execute();
        } catch (Exception e) {
            e.printStackTrace();
        }


    }

    public static void deleteById(Long id) {
        String string = String.format("delete FROM tmsord where doc_entry = %d", id).toString();
        log.info(string);
        try {
            dbConn.prepareStatement(string).execute();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static List<TmsordVO> getList(TmsordQueryForm queryForm) {
        StringBuilder stringBuilder = new StringBuilder("SELECT doc_entry,user_id,user_name,mac_id,mac_name, auth_date_time,auth_date,auth_time,direction,card_id FROM tmsord where 1=1  ");
        if (queryForm.getAuthDateTimeFr() != null) {
            stringBuilder.append(String.format(" and CONVERT(varchar(19),  auth_date_time,20 )  >= CONVERT(varchar(100), '%s' ,200) ", queryForm.getAuthDateTimeFr()));
        }
        if (queryForm.getAuthDateTimeTo() != null) {
            stringBuilder.append(String.format(" and CONVERT(varchar(19),  auth_date_time,20 )  <= CONVERT(varchar(100), '%s' ,200) ", queryForm.getAuthDateTimeTo()));
        }
        if (queryForm.getUserName() != null && queryForm.getUserName().length() > 0) {
            stringBuilder.append(String.format(" and user_name = '%s'", queryForm.getUserName()));
        }
        if (queryForm.getAbsId() != null && queryForm.getAbsId().length() > 0) {
            stringBuilder.append(String.format(" and CONVERT(varchar(7), auth_date_time,20 ) = '%s'", queryForm.getAbsId()));

        }
        stringBuilder.append(" order by user_id,auth_date_time ");

        log.info(stringBuilder.toString());
        ArrayList<TmsordVO> tmsordVOS = new ArrayList<>();
        try {
            ResultSet res = dbConn.prepareStatement(stringBuilder.toString()).executeQuery();
            while (res.next()) {
                String docEntry = res.getString("doc_entry");
                String userId = res.getString("user_id");
                String userName = res.getString("user_name");
                String macName = res.getString("mac_name");
                String macId = res.getString("mac_id");
                String cardId = res.getString("card_id");
                String createDateTime = res.getString("auth_date_time");

                String createDate = res.getString("auth_date");
                String createTime = res.getString("auth_time");
                String direction = res.getString("direction");
                TmsordVO tmsordVO = new TmsordVO();
                tmsordVO.setDocEntry(Long.valueOf(docEntry));
                tmsordVO.setUserId(userId);
                tmsordVO.setUserName(userName);
                tmsordVO.setMacId(macId);
                tmsordVO.setMacName(macName);
                tmsordVO.setCardId(cardId);
                tmsordVO.setAuthDateTime(createDateTime);
                tmsordVO.setAuthDate(createDate);
                tmsordVO.setDirection(direction);
                tmsordVOS.add(tmsordVO);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return tmsordVOS;
    }
}
